<?php
/**
 * Date: 2024.8
 * Author: kahn
 * Mysql数据驱动, 实现了函数式查询和链式查询
 * 引入thinkorm后, 该文件废弃不用
 */
namespace driver;

class Mysql
{
    /**
     * 静态变量, 存储数据库链接, 避免多次连同一数据库
     * @var array
     */
    private static $pdos = [];

    /**
     * 当前实例数据库链接
     * @PDO
     */
    private $pdo;

    /**
     * 数据库配置名称, 具体配置从ini文件获取
     * @var string
     */
    protected $_db_name;

    /**
     * 表名
     * @var string
     */
    protected $_table_name; //表名

    /**
     * 最后一条执行的sql语句
     * @var string
     */
    private $sql;

    /**
     * 执行update, insert操作时, pdo需要传入的实参
     * @var
     */
    private $params;

    /**
     * 链式操作所需数据
     * @var array
     */
    private array $chainData;

    /**
     * @param string $table_name
     * @param string $db_name
     */
    public function __construct(string $table_name='', string $db_name = 'default'){
        $this->_db_name = $db_name;
        $this->_table_name = $table_name;
        $this->params = [];
        $this->connect();
        $this->initChainData();
    }

    private function initChainData(): void
    {
        $this->chainData = [
            'table' => $this->_table_name,
            'field' => '*',
            'and' => [],
            'or' => [],
            'having' => [],
            'order' => [],
            'set' => [],
            'value' => [],
            'limit' => [],
            'group' => [],

            'join' => [],
            'on' => [],
        ];
    }

    /**
     * 获取数据库配置
     * @param $name string
     * @return mixed
     */
    private function getConfig(string $name) {
        return \Yaf_Registry::get("config")->mysql->config->$name->toArray();
    }


    /**
     * 连接数据库
     * @return void
     */
    private function connect(): void
    {
        $config = $this->getConfig($this->_db_name);
        if(!isset(self::$pdos[$this->_db_name])) {
            $port = $config['port'] == 3306 ? '' : ':' . $config['port'];
            $pdo = new \PDO("mysql:host={$config['host']}{$port};dbname={$config['db']}", $config['user'], $config['password']);
            $pdo->setAttribute(\PDO::ATTR_TIMEOUT, 5);
            $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

            self::$pdos[$this->_db_name] = $pdo;
        }
        $this->pdo = self::$pdos[$this->_db_name];
    }

    /**
     * 链式操作-查询字段
     * @param string|array $field
     * @return $this
     */
    public function field(string|array $field, $table=null): static
    {
        $this->chainData['field'] = $field;
        return $this;
    }

    /**
     * 获取查询字段
     * @return string
     */
    private function _field(): string
    {
        if($this->chainData['field'] == '*'){
            return '*';
        }

        //对字段加反引号
        if(is_string($this->chainData['field'])){
            $this->chainData['field'] = explode(',',$this->chainData['field']);
        }
        foreach($this->chainData['field'] as &$v){
            $v = trim($v);
            if(preg_match('/\bas\b|\s|\(|\./', $v) == 0){
                $v = "`$v`";
            }
        }
        return implode(', ',$this->chainData['field']);
    }

    /**
     * 链式操作-and条件
     * @param ...$argv
     * @return $this
     */
    public function where(mixed ...$argv): static
    {
        $this->chainData['and'][] = $argv;
        return $this;
    }

    /**
     * 链式操作-or条件
     * @param ...$argv
     * @return $this
     */
    public function or(mixed ...$argv): static
    {
        $this->chainData['or'][] = $argv;
        return $this;
    }

    /**
     * 获取or和and条件
     * @return string
     */
    private function _where():string
    {
        $and = $this->getCondition('and');
        $or = $this->getCondition('or');

        return $or !== '' ? " WHERE ($and) OR $or" : ($and !== '' ? " WHERE $and" : '');
    }

    /**
     * 链式操作-查询表
     * @param $table
     * @return $this
     */
    public function table(string $table): static
    {
        $this->chainData['table'] = $table;
        return $this;
    }

    /**
     * 获取表
     * @return string
     */
    private function _table(): string
    {
        return "`{$this->chainData['table']}`";
    }

    /**
     * 链式操作-group by
     * @param $group
     * @return $this
     */
    public function group(string $group): static
    {
        $this->chainData['group'][] = $group;
        return $this;
    }

    /**
     * 获取group by
     * @return string
     */
    private function _group(): string
    {
        $sql = '';
        foreach($this->chainData['group'] as $v){
            $sql .= ", `$v`";
        }
        return $sql == '' ? '' : " GROUP BY" . ltrim($sql, ',');
    }

    /**
     * 链式操作-having, 只支持and条件
     * @param ...$argv
     * @return $this
     */
    public function having(mixed ...$argv): static
    {
        $this->chainData['having'][] = $argv;
        return $this;
    }

    /**
     * 获取having
     * @return string
     */
    private function _having(): string
    {
        return empty($this->chainData['having']) ? '' : " HAVING {$this->getCondition('having')}";
    }

    /**
     * 链式操作-order, 支持多order
     * @param ...$argv
     * @return $this
     */
    public function order(string ...$argv): static
    {
        $this->chainData['order'][] = $argv;
        return $this;
    }

    /**
     * 获取order
     * @return string
     */
    private function _order(): string
    {
        $sql = '';
        foreach($this->chainData['order'] as $r){
            switch (count($r)){
                case 1:
                    $sql .= ", {$r[0]}";
                    break;
                case 2:
                    $s = strtoupper($r[1]);
                    if($s == 'ASC' || $s == 'DESC'){
                        $sql .= ", `{$r[0]}` $s";
                    }
                    break;
            }
        }
        if($sql != ''){
            $sql = ' ORDER BY ' . ltrim($sql, ", ");
        }
        return  $sql;
    }

    /**
     * 链式操作-limit
     * @param $start
     * @param $size
     * @return $this
     */
    public function limit(int ...$argv): static
    {
        $this->chainData['limit'] = $argv;
        return $this;
    }

    /**
     * 获取limit
     * @return string
     */
    private function _limit(): string
    {
        $sql = '';
        switch (count($this->chainData['limit']))
        {
            case 1:
                $sql = " LIMIT {$this->chainData['limit'][0]}";
                break;
            case 2:
                $sql = " LIMIT {$this->chainData['limit'][0]}, {$this->chainData['limit'][1]}";
        }

       return $sql;
    }

    /**
     * 链式操作-设置更新字段
     * @param array $set
     * @return $this
     */
    public function set(array $set): static
    {
        $this->chainData['set'] = $set;
        return $this;
    }

    /**
     * 获取更新字段
     * @return string
     */
    private function _set(): string
    {
        return $this->getCode($this->chainData['set']);
    }

    /**
     * 链式操作-设置插入字段
     * @param array $value
     * @return $this
     */
    public function value(array $value): static
    {
        $this->chainData['value'] = $value;
        return $this;
    }

    /**
     * 获取插入字段
     * @return string
     */
    private function _value(): string
    {
        return $this->getCode($this->chainData['value']);
    }


    /**
     * 链式操作-执行多行查询
     * @return array|null
     */
    public function lgets(): ?array
    {
        $sql = "SELECT {$this->_field()} FROM {$this->_table()}{$this->_where()}{$this->_group()}{$this->_having()}{$this->_order()}{$this->_limit()}";
        return $this->_fetch($sql, 1);
    }

    /**
     * 链式操作-执行单行查询
     * @return array|null
     */
    public function lget(): ?array
    {
        $sql = "SELECT {$this->_field()} FROM {$this->_table()}{$this->_where()}";
        return $this->_fetch($sql, 0);

    }

    /**
     * 链式操作-执行数据是否存在
     * @return bool
     */
    public function lexist(): bool
    {
        $sql = "SELECT 1 FROM {$this->_table()}{$this->_where()}";
        $row = $this->_fetch($sql,0);
        return (bool)$row;
    }

    /**
     * 链式操作-执行获取单个字段值
     * @param $key
     * @return mixed|null
     */
    public function lscaler($key): mixed
    {
        $sql = "SELECT {$this->_field()} FROM {$this->_table()}{$this->_where()}";
        $row = $this->_fetch($sql,0);
        return $row ? $row[$key] ?? null : null;
    }

    /**
     * 链式操作-执行获取行数
     * @return int
     */
    public function lcount(): int
    {
        $sql = "SELECT COUNT(1) AS c FROM {$this->_table()}{$this->_where()}";
        $row = $this->_fetch($sql,0);
        return $row ? $row['c'] : 1;
    }

    /**
     * 链式操作-执行插入
     * @param bool $resId
     * @return bool|int|null
     */
    public function ladd(bool $resId = true) : ?int
    {
        $sql = "INSERT INTO {$this->_table()} SET {$this->_value()}";
        $res = $this->execute($sql);
        return  $resId ? ($res ? $this->getLastId() : null) : $res;
    }

    /**
     * 链式操作-执行更新
     * @return bool
     */
    public function lupdate(): ?int
    {
        $sql = "UPDATE {$this->_table()} SET {$this->_set()}{$this->_where()}";
        return $this->execute($sql);
    }

    /**
     * 链式操作-执行删除
     * @return bool
     */
    public function ldelte(): ?int
    {
        $sql = "DELETE FROM {$this->_table()}{$this->_where()}";
        return $this->execute($sql);
    }

    /**
     * @param string $con
     * @return string
     */
    private function getCondition(string $con = 'and'): string
    {
        $sql = '';
        $relation = $con == 'having' ? 'AND' : strtoupper($con);
        foreach($this->chainData[$con] as $w){
            switch (count($w)){
                case 1:
                    $sql .= " $relation {$w[0]}";
                    break;
                case 2:
                    $sql .= " $relation `{$w[0]}` = {$this->__value($w[1])}";
                    break;
                case 3:
                    $sql .= " $relation `{$w[0]}` {$w[1]} {$this->__value($w[2])}";
                    break;
                case 4:
                    if($w[1] == 'between' || $w[1] == 'BETWEEN'){
                        $sql .= " $relation `{$w[0]}` BETWEEN {$this->__value($w[2])} AND {$this->__value($w[3])}";
                    }
                    break;
            }
        }
        return ltrim($sql, " $relation");
    }

    private function __value($v){
        switch (gettype($v)){
            case 'string':
                return "'$v'";
            case 'array':
                foreach ($v as &$_v){
                    $_v = $this->__value($_v);
                }
                return '(' . implode(',',$v) . ')';
            default:
                return $v;
        }
    }

    /**
     * 开始一个事务
     */
    public function beginTrans(): void
    {
       $this->pdo->beginTransaction(); 
    }

    /**
     * 提交事务
     */
    public function commit(): void
    {
        $this->pdo->commit();
    }

    /**
     * 在事务commit前回滚
     */
    public function rollBack(): void
    {
        $this->pdo->rollBack();
    }

    /**
     * 获取最后一条sql语句
     * @return string
     */
    public function getLastSql(): string
    {
        return $this->sql;
    }


    /**
     * @param $str
     * @return string
     */
    public function quote(string $str): string
    {
        return $this->pdo->quote($str);
    }

    /**
     * 作用:获取数据表里的栏位
     * 返回:表字段结构
     * 类型:数组
     */
    public function getFields(string $table) {
        $stmt = $this->pdo->query("DESCRIBE $table");
        $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        $stmt = null;
        return $result;
    }

    /**
     * 作用:获得最后INSERT的主鍵ID
     * 返回:最后INSERT的主鍵ID
     * 类型:数字
     */
    public function getLastId(): ?int
    {
        return $this->pdo->lastInsertId();
    }

    /**
     * 作用:执行INSERT\UPDATE\DELETE
     * 返回:执行语句影响行数
     * 类型:数字
     */
    public function execute(string $sql): ?int
    {
        \Log::debug('SQL::',['sql'=>$sql,'params'=>$this->params]);
        $this->sql = $sql;
        try{
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($this->params);
            $this->params = [];
            $this->initChainData();
            return  $stmt->rowCount();
        }catch (\PDOException $e){
            \Log::error("pdo exception",['msg'=>$e->getMessage(),'line'=>$e->getTraceAsString(), $this->getLastSql()]);
            return null;
        }
    }

    /**
     * 作用:执行select
     * 返回:执行语句影响行数
     * 类型:数字
     */
    public function query(string $sql): ?array
    {
        $this->sql = $sql;
        if(!stristr($sql,"select")){
            return $this->pdo->query($sql);

        }else{
            try{
                $stmt = $this->pdo->prepare($sql);
                $this->params = [];
                $stmt->execute($this->params);
                $stmt->setFetchMode (\PDO::FETCH_ASSOC);
                $result = $stmt->fetchAll();
                $this->params = [];
                $this->initChainData();
                return $result;

            }catch(\PDOException $e){
                \Log::error("pdo exception",['msg'=>$e->getMessage(),'line'=>$e->getTraceAsString(), $this->getLastSql()]);
                return null;
            }
        }
    }

    /**
     * 获取要操作的数据
     * 返回:合併后的SQL語句
     * 类型:字串
     */
    private function getCode(array $args): string
    {
        $code = '';
        foreach($args as $k => $v){
            $code .= "`$k`= :$k ,";
            $this->params[":$k"] = $v;
        }
        return $code == '' ? '' :   rtrim($code, ' ,');
    }

    /**
     * 对该表进行大量增删改前优化性能
     * @param $table
     */
    public function optimizeTable(?string $table=null) {
        $table ??= $this->_table_name;
        $sql = "OPTIMIZE TABLE $table";
        self::execute($sql);
    }


    /**
     * 具体执行查询语句
     * @param string $sql
     * @param int $type
     * @return array|null
     */
    private function _fetch(string $sql, int $type): null|bool|array
    {
        $this->sql = $sql;
        \Log::debug("SQL: $sql");
        try{
            $result = array ();
            $stmt = $this->pdo->prepare($sql);
            $this->params = [];
            $this->initChainData();
            $stmt->execute($this->params);
            $stmt->setFetchMode (\PDO::FETCH_ASSOC);
            switch($type){
                case 0 :
                    $result = $stmt->fetch();
                    break;
                case 1 :
                    $result = $stmt->fetchAll();
                    break;
                case 2 :
                    $result = $stmt->rowCount();
                    break;
            }
            return $result;
        }catch(\PDOException $e){
            \Log::error("pdo exception",['msg'=>$e->getMessage(),'line'=>$e->getTraceAsString(), $this->getLastSql()]);

            return null;
        }

    }

}
